package model.dao.registerOrder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import db.dbconnection;

import log.MyLogger;
import model.service.Device;
import model.service.DeviceType;



public class DeviceDAOImpl{

	String sql = "" ;
	db.dbconnection dbconnection ;
	java.sql.Connection myConnection ;
	Statement st ;
	
	public Device getDeviceById(int deviceId) {
		Device device = new Device();
		device.setDevice(deviceId);
		try {
			dbconnection = new dbconnection();
			myConnection = dbconnection.getConnection();
			Statement st = myConnection.createStatement();
			String sql = "SELECT * FROM device WHERE device = "+deviceId;
			ResultSet resultSet = st.executeQuery(sql);
			DeviceTypeDAOImpl deviceTypeDAOImpl = new DeviceTypeDAOImpl();
			while(resultSet.next()){
				device.setDeviceType(deviceTypeDAOImpl.getDeviceTypeById(resultSet.getInt("device_type_fk")));
				device.setName(resultSet.getString("name"));
				device.setRegNo(resultSet.getString("reg_no"));
				device.setDescription(resultSet.getString("description"));
				device.setModel(resultSet.getString("model"));
				device.setManufacturer(resultSet.getString("manufacturer"));
			}
			this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("DeviceDAOImpl.getDeviceById()", e.getMessage());
		}
		return device;
	}

	public List<Device> getDevices(DeviceType deviceType, String name, String regNo, String description,String model, String manufacturer) {
		List<Device> _return = new ArrayList<Device>();
		try {
			myConnection = dbconnection.getConnection();
			Statement st = myConnection.createStatement();
			String sql = "SELECT device from f_get_devices("+deviceType.getDeviceType()+",'"+name+"','"+regNo+"','"+description+"','"+model+"','"+manufacturer+"')";
			//MyLogger.LogMessage(sql);
			ResultSet resultSet = st.executeQuery(sql);
			while(resultSet.next()){
				Device d = getDeviceById(resultSet.getInt("device"));
				_return.add(d);
			}
			this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("DeviceDAOImpl.getDevices()", e.getMessage());
		}
		return _return;
	}

	public List<Device> getCustomerDevices(DeviceType deviceType, String name,String regNo, String description, String model, String manufacturer, String customerName) {
		List<Device> _return = new ArrayList<Device>();
		try {
			Connection c = dbconnection.getConnection();
			Statement st = c.createStatement();
			String sql = "SELECT device from f_get_devices_with_customer("+deviceType.getDeviceType()+",'"+name+"','"+regNo+"','"+description+"','"+model+"','"+manufacturer+"','"+customerName+"')";
			//MyLogger.LogMessage(sql);
			ResultSet resultSet = st.executeQuery(sql);
			while(resultSet.next()){
				Device d = getDeviceById(resultSet.getInt("device"));
				_return.add(d);
			}
			this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("DeviceDAOImpl.getCustomerDevices()", e.getMessage());
		}
		return _return;
	}

	public int insertDevice(Device d) {
		int result = 0;
		try {
			dbconnection = new dbconnection();
			myConnection = dbconnection.getConnection();
			String sql = "INSERT INTO device (name) VALUES ('"+d.getName()+"')";
			PreparedStatement pstmt = myConnection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		    pstmt.executeUpdate();
		    ResultSet keys = pstmt.getGeneratedKeys();
		    keys.next();
		    result = keys.getInt(1);
		    this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("DeviceDAOImpl.insertDevice()", e.getMessage());		
		}
		
		return result;
	}

	public List<Device> getAlldevices() {
		List<Device> devices = new ArrayList<Device>();
		try {
			myConnection = dbconnection.getConnection();
			Statement st = myConnection.createStatement();
			String sql = "SELECT device FROM device";
			ResultSet r = st.executeQuery(sql);
			while(r.next()){
				int id = r.getInt("device");
				devices.add(getDeviceById(id));
			}
			this.dbconnection.close();
		} catch (Exception e) {
//			MyLogger.Log("DeviceDAOImpl.getAlldevices", e.getMessage());
		};
		return devices;
	}
	
	public Device searchDevice(String name) {
		Device result = null;
		try {
			dbconnection = new dbconnection();
			myConnection = dbconnection.getConnection();
			Statement st = myConnection.createStatement();
			String sql = "SELECT device, name FROM device WHERE name LIKE '%"+name+"%' LIMIT 1";
			ResultSet r = st.executeQuery(sql);
			r.next();
			result = new Device();
			System.out.println(r.getString("name"));
			result.setDevice(r.getInt("device"));
			result.setName(r.getString("name"));
			this.dbconnection.close();
			return result;
		} catch (Exception e) {
//			MyLogger.Log("DeviceDAOImpl.searchDevice", e.getMessage());
			return null;
		}
	}



}
